package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import entity.Contents;
import util.DBHelper;

public class ContentsDAO {
	private Connection conn = null;
	private Statement st = null;

	public ContentsDAO() {
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Contract", "sa",
					"123456");
			st = conn.createStatement();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public synchronized void release() {
		if (st != null) {
			try {
				st.close();
			} catch (Exception e) {
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public synchronized List loadAllContents() throws Exception {
		List result = new ArrayList();
		try {
			ResultSet rs = st.executeQuery("select * from Contents where cstate='1' OR cstate='0'");
			while (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCcompany(rs.getString("ccompany"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPrice1(rs.getString("price1"));
				contents.setPrice2(rs.getString("price2"));
				contents.setPrice3(rs.getString("price3"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setPaytimes(rs.getString("paytimes"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				result.add(contents);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}
		return result;
	}

	public synchronized List loadAllContentsPassing(String company) throws Exception {
		List result = new ArrayList();
		try {
			ResultSet rs = st.executeQuery("select * from Contents where ccompany='" + company + "' and cstate='1'");

			while (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCcompany(rs.getString("ccompany"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPrice1(rs.getString("price1"));
				contents.setPrice2(rs.getString("price2"));
				contents.setPrice3(rs.getString("price3"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setPaytimes(rs.getString("paytimes"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				result.add(contents);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}
		return result;
	}

	public synchronized List loadAllContents2(String company) throws Exception {
		List result = new ArrayList();
		try {
			ResultSet rs = st.executeQuery("select * from Contents where ccompany='" + company + "' and cstate='2'");
			while (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCcompany(rs.getString("ccompany"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPrice1(rs.getString("price1"));
				contents.setPrice2(rs.getString("price2"));
				contents.setPrice3(rs.getString("price3"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setPaytimes(rs.getString("paytimes"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				result.add(contents);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}
		return result;
	}

	public synchronized List loadAllContentsByCompany(String company, String userno) throws Exception {
		List result = new ArrayList();
		try {
			ResultSet rs = st.executeQuery("select * from Contents where ccompany='" + company
					+ "' and (cstate='1' OR cstate='0') and drafter='" + userno + "'");
			while (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCcompany(rs.getString("ccompany"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPrice1(rs.getString("price1"));
				contents.setPrice2(rs.getString("price2"));
				contents.setPrice3(rs.getString("price3"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setPaytimes(rs.getString("paytimes"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				result.add(contents);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}
		return result;
	}

	public Contents getContentsByCno(String cno) {
		try {
			String sql = "select * from Contents where cno='" + cno + "';"; // SQL语句
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {
				Contents contents = new Contents();
				contents.setCno(rs.getString("cno"));
				contents.setCname(rs.getString("cname"));
				contents.setCcompany(rs.getString("ccompany"));
				contents.setCprice(rs.getString("cprice"));
				contents.setPrice1(rs.getString("price1"));
				contents.setPrice2(rs.getString("price2"));
				contents.setPrice3(rs.getString("price3"));
				contents.setPaystate(rs.getString("paystate"));
				contents.setPaytimes(rs.getString("paytimes"));
				contents.setCstate(rs.getString("cstate"));
				contents.setMclause(rs.getString("mclause"));
				contents.setDrafter(rs.getString("drafter"));
				contents.setApprover(rs.getString("approver"));
				contents.setCstart(rs.getString("cstart"));
				contents.setCend(rs.getString("cend"));
				contents.setOther(rs.getString("other"));
				return contents;
			} else {
				return null;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}

	}

	public synchronized void addContents(Contents contents) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + contents.getCno() + "'");
			if (rs.next()) {
				throw new Exception("合同号不能重复");
			} else {
				String sql = "insert into contents(cno,cname,ccompany,cprice,paytimes,paystate,cstate,mclause,drafter,approver,cstart,cend,other,price1,price2,price3) values('"
						+ contents.getCno() + "','" + contents.getCname() + "','" + contents.getCcompany() + "','"
						+ contents.getCprice() + "','" + contents.getPaytimes() + "','" + contents.getPaystate() + "','"
						+ 1 + "','" + contents.getMclause() + "','" + contents.getDrafter() + "','"
						+ contents.getApprover() + "','" + contents.getCstart() + "','" + contents.getCend() + "','"
						+ contents.getOther() + "','" + contents.getPrice1() + "','" + contents.getPrice2() + "','"
						+ contents.getPrice3() + "')";
				st.execute(sql);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void saveContents(Contents contents) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + contents.getCno() + "'");
			if (!rs.next()) {
				throw new Exception("合同号不存在");
			}
			String sql = "update contents(cno,cname,ccompany,cprice,paytimes,paystate,cstate,mclause,drafter,approver,cstart,cend,other,,price1,price2,price3) values('"
					+ contents.getCno() + "','" + contents.getCname() + "','" + contents.getCcompany() + "','"
					+ contents.getCprice() + "','" + contents.getPaytimes() + "','" + contents.getPaystate() + "','" + 1
					+ "','" + contents.getMclause() + "','" + contents.getDrafter() + "','" + contents.getApprover()
					+ "','" + contents.getCstart() + "','" + contents.getCend() + "','" + contents.getOther() + "','"
					+ contents.getPrice1() + "','" + contents.getPrice2() + "','" + contents.getPrice3() + "')";
			st.execute(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void passContents(String cno, String userno) throws Exception {
		try {
			String sql1 = "update Contents set cstate='2',approver='" + userno + "' where cno='" + cno + "'";
			st.execute(sql1);
			ResultSet rs = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (rs.next()) {
				String ccompany = rs.getString("ccompany");
				String paytimes = rs.getString("paytimes");
				String price1 = rs.getString("price1");
				String price2 = rs.getString("price2");
				String price3 = rs.getString("price3");
				String cprice = rs.getString("cprice");
				String paystate = rs.getString("paystate");
				String cstart = rs.getString("cstart");
				String cend = rs.getString("cend");
				if (paystate.equals("1")) {
					paystate = "收";
				} else {
					paystate = "付";
				}
				String sql2 = "insert into execution(cno,context,cstart,cend,ccompany) values('" + cno + "','未执行','"
						+ cstart + "','" + cend + "','" + ccompany + "')";
				st.execute(sql2);
				String sql3 = "insert into payment(cno,paystate,payprice,price1,price2,price3,contractp,paytimes,ccompany) values('"
						+ cno + "','" + paystate + "'," + "'0','" + price1 + "','" + price2 + "','" + price3 + "','"
						+ cprice + "','" + paytimes + "','" + ccompany + "')";
				st.execute(sql3);
			}

		} catch (

		SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void delContents(String cno) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (!rs.next()) {
				throw new Exception("合同号不存在");
			}
			String sql = "delete from contents where cno='" + cno + "'";
			st.execute(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}
	}

	public synchronized List<Integer> acquire() throws Exception {
		List<Integer> data = new ArrayList<Integer>();
		try {
			ResultSet rsa = st.executeQuery("select count(*) from execution where context='未执行'");
			if (rsa.next()) {
				data.add(rsa.getInt(1));
			}
			ResultSet rsb = st.executeQuery("select count(*) from execution where context='执行中'");
			if (rsb.next()) {
				data.add(rsb.getInt(1));
			}
			ResultSet rsc = st.executeQuery("select count(*) from home");
			if (rsc.next()) {
				data.add(rsc.getInt(1));
			}
			return data;
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}

	}

	public synchronized List<Integer> geta() throws Exception {
		List<Integer> data = new ArrayList<Integer>();
		try {
			ResultSet rsa = st.executeQuery("select count(*) from execution where cend<'2017-07-20'");
			if (rsa.next()) {
				data.add(rsa.getInt(1));
			}
			ResultSet rsb = st.executeQuery("select count(*) from execution where cend<'2017-06-15'and context='执行中'");
			if (rsb.next()) {
				data.add(rsb.getInt(1));
			}
			return data;
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}

	}

	public void returncnContents(String cno) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from contents where cno='" + cno + "'");
			if (!rs.next()) {
				throw new Exception("合同号不存在");
			}

			String sql1 = "update Contents set cstate='0' where cno='" + cno + "'";
			st.execute(sql1);

		} catch (

		SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void contents_editsave(Contents contents) throws Exception {
		try {
			Connection conn = null;
			PreparedStatement st = null;
			String paystate="";
			if (contents.getPaystate().equals("收"))
				paystate = "1";
			else
				paystate = "2";
			conn = DBHelper.getConnection();
			String sql = "update contents set cprice='" + contents.getCprice() + "',paytimes='" + contents.getPaytimes() + "',paystate='" + paystate
					+ "',cstate='" + 1 + "',mclause='" + contents.getMclause()+ "',cstart='" + contents.getCstart() + "',cend='" + contents.getCend()
					+ "',price1='"+contents.getPrice1()+"',price2='"+contents.getPrice2()+"' ,price3='"+contents.getPrice3()+"' where cno='" + contents.getCno() + "'";
			st = conn.prepareStatement(sql);
			ResultSet rs = st.executeQuery();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

}
